package org.gw.ylc.base.repositories;

import org.gw.ylc.base.model.Student;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.Date;
import java.util.List;

public interface StudentRepository extends CrudRepository<Student, String> {
	public Student findByXh(String xh);

	public Student findById(String id);

	public Student findByXhOrName(String xh, String name);

	@Query("select s from Student s left join s.nativeplace  where ( ?1 = null or s.xh like ?1 or s.name like ?1 ) and (s.grade = ?2 or ?2 = null) and"
			+ " ( s.department.id = ?3 or ?3 = null) and ( s.major.id = ?4 or ?4 = null) and (s.sex = ?5 or ?5 = null) and (?6 = null or s.policital.code=?6) "
			+ "and (?7=null or s.birthdate>=?7) and (?8=null or s.birthdate<?8) and (?9=null or s.midschool like ?9) "
			+ "and (?10=null or s.country.code=?10) and (?11=null or s.nativeplace.code=?11) and (?12=null or s.telephone like ?12 or s.email like ?12) "
			+ "and (?13=null or s.nativeplace.name like ?13) order by s.grade desc,s.department desc,s.major desc,s.xh asc")
	public Page<Student> queryStudent(String xh, String grade, String department, String major, Integer sex,
                                      String policital, Date startBirthDate, Date endBirthDate, String midschool, String country,
                                      String nativeplace, String telOrEmail, String nativeplaceStr, Pageable page);

	@Query("select s from Student s, GradeMajor g where ((g.grade=s.grade or g.grade is null) and g.major=s.major and g.department = s.department) and g.id in ?1 and (s.department.id = ?2 or ?2 is null)")
	public List<Student> queryByGradeMajors(List<String> gradeMajors, String department);

	List<Student> findByClassName(String className);

}
